---
title: 索引的使用原则
description: 如何通过索引让 SQL 查询效率最大化
---
使用索引的目的是为了提高 SQL 的查询效率，那么该如何通过索引让效率最大化？

![](https://img.wkq.pub/spring/81147e99e2533126533500a087086ef4.webp)

## 创建索引有哪些规律

创建索引有一定的规律。当这些规律出现的时候，我们就可以通过创建索引提升查询效率。
### 1. 字段的数值有唯一性的约束
索引本身可以起到约束的作用，比如唯一索引、主键索引都是可以起到唯一性约束的，因此在我们的数据表中，如果某个字段是唯一的，就可以直接创建唯一性索引，或者主键索引。

### 2. 频繁作为 WHERE 查询条件的字段
在数据量大的时候，某个字段在 SQL 查询的 WHERE 条件经常被使用到，那么就需要给这个字段创建索引了。创建普通索引就可以大幅提升数据查询的效率。


### 3.需要经常用到 GROUP BY 和 ORDER BY 的列
索引就是让数据按某种顺序进行存储和检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要对分组或者排序的字段进行索引。

对于同时有 GROUP BY 和 ORDER BY 的情况。就需要同时进行 GROUP BY 和 ORDER BY,那么是不是需要单独创建 user_id 和 comment_time 的索引呢？

实际上多个单列索引在多条件查询时只会生效一个索引（MySQL 会选择其中一个限制最严格的作为索引），所以在多条件查询的时候最好创建联合索引。

### 4. UPDATE 、DELETE 的 WHERE 条件列，一般也需要创建索引

对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。原因是我们需要先根据 WHERE 条件列检索出这条记录，然后再对它进行更新和删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更加明显，这是因为非索引字段更新不需要对索引进行维护。


:::tip
如果索引太多了，在更新数据的时候，如果涉及到了索引更新，就会造成负担。
:::
### 5. DISTINCT 字段需要创建索引
有时候需要对某个字段去重，使用 DISTINCT ，那么对这个字段创建索引，也会提升查询效率。

### 6. 做多表 JOIN 连接操作时
首先，连接表的数量不要超过 3 张，因为每增加一张就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。

其次，对 WHERE 条件创建索引，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。

最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。

如果我们不使用 WHERE 条件查询，而是直接采用 JOIN...ON...进行同步的话，即使使用了各种优化手段，总的运行时间也很长。



## 什么时候不需要创建索引
索引不是万能的，有一些情况是不需要创建索引的。

### 1. WHERE条件、 GROUP BY 、ORDER BY 里用不到的字段不需要创建索引
索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的。

### 2. 如果表记录太少，也是不需要创建索引的。

如果表记录太少，比如少于 1000 个，那么是不需要创建索引的。是否创建索引对查询效率提升不大。

### 3. 字段中如果有大量重复数据，也不用创建索引
一般重复度高于 10 % ，才使用创建索引。比如性别字段
### 4. 频繁更新的字段
频繁更新的字段不一定要创建索引。因为更新数据的时候，也需要更新索引，如果索引太多，在更新索引的时候也会造成负担，从而影响效率。

## 索引失效的情况
### 1. 如果索引进行了表达式计算
可以使用  EXPLAIN  关键字来查看 MYSQL 中的一条 SQL 语句的执行计划，比如：
```sql
mysql> EXPLAIN SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_id+1 = 900001;
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table           | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | product_comment | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 996500 |   100.00 | Using where |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
```
如果对索引进行了表达式计算，索引就失效了。这是因为我们需要把索引字段的取值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的是全表扫描的方式。

```sql title='为了避免索引失效，对 SQL 进行重写'
SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_id = 900000'
```
### 2. 索引使用了函数，则会失效

```sql title='对 comment_text 的前三位为 abc 的内容进行条件筛选'
mysql> EXPLAIN SELECT comment_id,user_id,comment_text FROM product_comment WHERE SUBSTRING(comment_text,1,3) = 'abc';
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table           | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | product_comment | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 996500 |   100.00 | Using where |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
```

对索引字段进行了函数操作，造成了索引失效，这里可以进行查询重写：
```sql
mysql> EXPLAIN SELECT comment_id,user_id ,comment_text FROM product_comment WHERE comment_text LIKE 'abc%';
+----+-------------+-----------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table           | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | product_comment | NULL       | range | c             | c    | 767     | NULL |  213 |   100.00 | Using index condition |
+----+-------------+-----------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
```

经过查询重写后，可以使用索引进行范围查询，从而提升查询效率。
### 3. 在 WHERE 子句中，如果在 OR 前的条件列进行了索引，而在 OR 后的条件没有进行索引。就会索引失效
OR 的含义就是两个只要满足一个即可，因此只有一个进行了索引是没有意义的，只要条件列没有索引，就会进行全表扫描，因此索引的条件列也会失效。
```sql
mysql> EXPLAIN SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_id = 900001 OR comment_text = '462eed7ac6e791292a79';
+----+-------------+-----------------+------------+-------------+---------------+-----------+---------+------+------+----------+-------------------------------------+
| id | select_type | table           | partitions | type        | possible_keys | key       | key_len | ref  | rows | filtered | Extra                               |
+----+-------------+-----------------+------------+-------------+---------------+-----------+---------+------+------+----------+-------------------------------------+
|  1 | SIMPLE      | product_comment | NULL       | index_merge | PRIMARY,c     | PRIMARY,c | 4,767   | NULL |    2 |   100.00 | Using union(PRIMARY,c); Using where |
+----+-------------+-----------------+------------+-------------+---------------+-----------+---------+------+------+----------+-------------------------------------+
1 row in set, 1 warning (0.00 sec)
```
如果把 comment_text 创建了索引，会使用 index merge, 简单来说就是对 comment_id 和 comment_text 分别进行了扫描，然后将这两个结果集进行了合并。这样做的好处就是避免了全表扫描。
### 4. 当我们使用LIKE进行模糊查询的时候，前面不能是 %
```sql
mysql> EXPLAIN SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_text LIKE '%abc';
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table           | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | product_comment | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 996500 |    11.11 | Using where |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
```
### 5. 索引列尽量设置为 NOT NULL 约束
MySQL 官方文档建议尽量将数据库的字段设置为 NOT NULL 约束，这样做的好处是可以更好的使用索引，节省空间，甚至加速 SQL 的运行。
判断索引列是否为 NOT NULL， 往往需要走全表扫描，因此我们最好在设计数据库的时候将字段设置为 NOT NULL约束。

可以将 INT 类型的字段，默认值设置为 0。将字符类型的默认值设置为空字符串 （''）

### 6. 在使用来联合索引的时候要注意最左侧原则
最左原则也就是需要从左到右使用索引，一条 SQL 语句可以只使用联合索引的一部分，但是需要从最左侧开始，否则就会失效。



